insert overwrite table jms_dm.dm_waybill_prescription_reach_details_dt partition(dt)
select  detail.taking_date     --    '揽收日期'
        ,detail.island          --    '是否桃花岛'
        ,detail.waybill_no       --    '运单号'
        ,detail.customer_code     --    '客户编号'
        ,detail.customer_name     --    '客户名称'
        ,detail.start_subordinate_agent_code     --    '始发代理区code'
        ,detail.start_subordinate_agent_name     --    '始发代理区name'
        ,detail.start_parent_network_code     --    '始发加盟商code'
        ,detail.start_parent_network_name     --    '始发加盟商name'
        ,detail.start_regional_id     --    '始发大区id'
        ,detail.start_regional_desc     --    '始发大区name'
        ,detail.start_provider_id     --    '始发省份id'
        ,detail.start_provider_desc     --    '始发省份name'
        ,detail.start_city_id     --    '始发城市id'
        ,detail.start_city_desc     --    '始发城市name'
        ,detail.start_area_id     --    '始发区id'
        ,detail.start_area_desc     --    '始发区name'
        ,detail.start_pick_network_code     --    '始发网点code'
        ,detail.start_pick_network_name     --    '始发网点name'
        ,detail.aim_subordinate_agent_code     --    '目的代理区code'
        ,detail.aim_subordinate_agent_name     --    '目的代理区name'
        ,detail.aim_parent_network_code     --    '目的加盟商code'
        ,detail.aim_parent_network_name     --    '目的加盟商name'
        ,detail.aim_regional_id     --    '目的大区id'
        ,detail.aim_regional_desc     --    '目的大区name'
        ,detail.aim_provider_id     --    '目的省份id'
        ,detail.aim_provider_desc     --    '目的省份name'
        ,detail.aim_city_id     --    '目的城市id'
        ,detail.aim_city_desc     --    '目的城市name'
        ,detail.aim_area_id     --    '目的区id'
        ,detail.aim_area_desc     --    '目的区name'
        ,detail.aim_destination_code     --    '目的网点code'
        ,detail.aim_destination_name     --    '目的网点name'
        ,detail.taking_time     --    '揽收时间'
        ,detail.warehous_time     --    '入仓时间'
        ,detail.send_time     --    '发件扫描时间'
        ,detail.start_center_code     --    '始发转运中心code'
        ,detail.start_center_name    --    '始发转运中心name'
        ,detail.start_centerarriva_time     --    '始发中心到件扫描时间'
        ,detail.arrival_task_no     --    '到车任务号'
        ,detail.type_one     --    '类型1'
        ,detail.start_centersend_time     --    '始发中心发件扫描时间'
        ,detail.departure_task     --    '始发发车任务单号'
        ,detail.start_centersend_departure_time -- 始发中心发车时间
        ,detail.aim_center_code     --    '目的转运中心code'
        ,detail.aim_center_name     --    '目的转运中心name'
        ,detail.aim_centerarriva_time     --    '目的中心到件扫描时间'
        ,detail.arrival_task  -- 目的中心到车任务单号
        ,detail.aim_centerarrival_departure_time -- 目的中心到车时间
        ,detail.type_two     --    '类型2'
        ,detail.aim_centersend_time     --    '目的中心发件扫描时间'
        ,detail.sign_network_code     --    '签收网点code'
        ,detail.sign_network_name     --    '签收网点name'
        ,detail.sign_subordinate_agent_code     --    '签收代理区code'
        ,detail.sign_subordinate_agent_name     --    '签收代理区name'
        ,detail.sign_parent_network_code     --    '签收加盟商code'
        ,detail.sign_parent_network_name     --    '签收加盟商name'
        ,detail.sign_regional_id     --    '签收大区id'
        ,detail.sign_regional_desc     --    '签收大区name'
        ,detail.sign_provider_id     --    '签收省份id'
        ,detail.sign_provider_desc     --    '签收省份name'
        ,detail.sign_city_id     --    '签收城市id'
        ,detail.sign_city_desc     --    '签收城市name'
        ,detail.sign_area_id     --    '签收区id'
        ,detail.sign_area_desc     --    '签收区name'
        ,detail.pres_sign_time    --    '时效签收时间'
        ,detail.real_sign_time     --    '实际签收时间'
        ,detail.deliverytotaltime  -- 派件总耗时
        ,detail.plan_sign_date  -- 规划应签收日期
        ,detail.dispatch_sign_time  -- 派件应签收时间
        ,detail.is_delivery_delay  -- 交件是否延误 0否1是
        ,detail.is_transport_delay  -- 转运是否延误 0否1是
        ,detail.is_sign_delay  -- 签收是否延误 0否1是
        ,detail.delay_type -- 延误类型
        ,detail.districtprescription  -- 区县时效
        ,detail.prescription  -- 实际运行时效
        ,detail.is_real_standard  -- 实际是否达标  1是0否
        ,detail.pres_prescription  -- 时效运行时效
        ,detail.is_pres_standard  -- 时效是否达标  1是0否
        ,detail.is_pres_sign  -- 时效签收
        ,detail.is_real_sign  -- 实际签收
        ,detail.economic_circle_type -- 经济圈名称 经济圈名称 1珠三角 2长三角 3京津冀
        ,case when detail.economic_circle_type in (1,2,3) then 1 else 0 end circle_type  --  类型是否经济圈 1是0否
        ,config.platform_effect  -- 平台时效
        ,case when detail.real_sign_time is null and datediff(current_date(),detail.taking_date)>config.platform_effect then 0
               when detail.real_sign_time is null and datediff(current_date(),detail.taking_date)<=config.platform_effect then null
               when detail.real_sign_time is not null and datediff(detail.real_sign_time,detail.taking_date)<=config.platform_effect then 1
               when detail.real_sign_time is not null and datediff(detail.real_sign_time,detail.taking_date)>config.platform_effect then 0 end as is_platform_real_standard  -- 平台实际是否达标  1是0否
         ,case when detail.pres_sign_time is null and datediff(current_date(),detail.taking_date)>config.platform_effect then 0
               when detail.pres_sign_time is null and datediff(current_date(),detail.taking_date)<=config.platform_effect then null
               when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,detail.taking_date)<=config.platform_effect then 1
               when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,detail.taking_date)>config.platform_effect then 0 end as is_platform_pres_standard  -- 平台时效是否达标  1是0否
        ,case when config.platform_effect is null then 0 else 1 end as platform_effect_type  -- 是否平台时效 1是0否
        ,taking_date as dt
        from (select * from jms_dm.dm_waybill_prescription_reach_details_dt_tmp2 where dt>='{{ execution_date | date_add(-6) | cst_ds }}' and dt<='{{ execution_date |  cst_ds }}') detail

        left join (select * from jms_ods.tab_platform_effect_config where dt='{{ execution_date |  cst_ds }}') config on detail.start_city_desc=config.start_city and detail.sign_city_desc=config.end_city
        and detail.taking_date>=from_unixtime(unix_timestamp(config.effect_start_time),'yyyy-MM-dd') and detail.taking_date<date_add(from_unixtime(unix_timestamp(config.effect_end_time),'yyyy-MM-dd'),1)
        left join (select waybill_no from jms_ods.tab_end_piece where end_code in (44,5) and dt='{{ execution_date |  cst_ds }}') end_piece on detail.waybill_no=end_piece.waybill_no
            left join (select waybill_no from jms_dm.dm_waybill_prescription_reach_details_dt where dt>='{{ execution_date | date_add(-13) | cst_ds }}' and dt<='{{ execution_date | date_add(-7) | cst_ds }}' ) exist on detail.waybill_no=exist.waybill_no
        where exist.waybill_no is null and (end_piece.waybill_no is null or end_piece.waybill_no='') ;